package cn.test.my.excel.poi;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import cn.test.my.excel.poi.annotation.Ignore;
import cn.test.my.excel.poi.annotation.SetCellStyle;
import cn.test.my.excel.poi.convert.Converter;
import cn.test.my.excel.poi.convert.DefaultConverterLoader;
import net.sf.cglib.beans.BeanMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import static org.apache.poi.ss.usermodel.CellType.BLANK;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
import static org.apache.poi.ss.usermodel.CellType.STRING;
import static org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType.FORMULA;

/**
 * excel 导入
 *
 * @author jiema
 * @since 2020/11/8 14:19
 */
@SuppressWarnings("unused")
public class POIDataReader {

	private static final Logger log = LoggerFactory.getLogger(POIDataReader.class);

	/**
	 * 输入流
	 */
	private final InputStream inputStream;

	/**
	 * execl
	 */
	private Workbook workbook;

	/**
	 * sheet对象
	 */
	private Sheet sheet;

	private Row row;

	@SuppressWarnings("unused")
	public POIDataReader(InputStream inputStream) {
		this.inputStream = inputStream;
		this.createWorkbook(inputStream);
	}

	/**
	 * 创建workbook
	 *
	 * @param inputStream input
	 */
	private void createWorkbook(InputStream inputStream) {
		try {
			//创建 excel
			workbook = WorkbookFactory.create(inputStream);
		}
		catch (Exception e) {
			log.error("Excel文件解析失败!", e);
		}
	}


	/**
	 * 读取Excel表格表头的内容
	 *
	 * @param sheetAt 第几个sheet
	 * @return String 表头内容的数组
	 */
	@SuppressWarnings("unused")
	public String[] readExcelTitle(int sheetAt) {
		String[] title = null;

		if (inputStream != null) {
			try {
				if (null == workbook) {
					workbook = WorkbookFactory.create(inputStream);
				}
				sheet = workbook.getSheetAt(sheetAt);
				row = sheet.getRow(0);
				// 标题总列数
				int colNum = row.getPhysicalNumberOfCells();
				title = new String[colNum];
				for (int i = 0; i < colNum; i++) {
					title[i] = getStringCellValue(row.getCell(i));
				}
			}
			catch (Exception e) {
				log.error("Excel文件解析失败!", e);
			}
		}

		return title;
	}

	/**
	 * 读取excel内容并转换成对象
	 *
	 * @param clazz 类型
	 * @param data excel数据
	 * @param <F> class type
	 * @return 数据集合
	 */
	public <F> List<F> readExcelContent(Class<F> clazz, List<String[]> data) {
		List<F> list = new ArrayList<>();

		Map<Field, SetCellStyle> fieldMap = new LinkedHashMap<>();
		Field[] fields = clazz.getDeclaredFields();
		// 筛选带注解字段
		for (Field value : fields) {
			if (value.isAnnotationPresent(Ignore.class)) {
				continue;
			}
			if (value.isAnnotationPresent(SetCellStyle.class)) {
				SetCellStyle col = value.getAnnotation(SetCellStyle.class);
				fieldMap.put(value, col);
			}
		}

		for (String[] rowData : data) {
			try {
				F obj = clazz.newInstance();
				Map<String, Object> map = new HashMap<>(16);
				for (Map.Entry<Field, SetCellStyle> entry : fieldMap.entrySet()) {
					Field fieldKey = entry.getKey();
					Class<?> filedClazz = fieldKey.getType();
					SetCellStyle style = fieldMap.get(fieldKey);
					Converter<?> converter = DefaultConverterLoader.getConverter(filedClazz);
					if (converter == null) {
						log.error("解析excel数据异常,缺少该类型[{}]转换器", filedClazz.getSimpleName());
						break;
					}
					else {
						Object fieldValue = converter.convertToJavaData(fieldKey, rowData[style.index()], style);
						map.put(fieldKey.getName(), fieldValue);
					}
				}
				BeanMap.create(obj).putAll(map);
				list.add(obj);
			}
			catch (Exception e) {
				log.error("解析excel数据异常", e);
			}
		}
		return list;
	}

	/**
	 * 读取内容并转换成对象
	 * (默认只读取第一个sheet数据)
	 *
	 * @param clazz 类型
	 * @param <F> class type
	 * @return 数据集合
	 */
	public <F> List<F> readExcelContent(Class<F> clazz) {
		List<F> list = new ArrayList<>();

		Map<Integer, List<String[]>> excelData = readExcelContent();
		if (excelData != null && excelData.size() > 0) {
			List<String[]> data = excelData.get(0);
			return readExcelContent(clazz, data);
		}
		else {
			log.warn("读取excel数据为空");
		}

		return list;
	}

	/**
	 * 值实际类型转化
	 *
	 * @param fieldMap 字段
	 * @param fieldKey key
	 * @param map 对象值
	 * @param rowData excel值
	 */
	private void convert(Map<Field, SetCellStyle> fieldMap, Field fieldKey, Map<String, Object> map,
			String[] rowData) {
		Class<?> filedClazz = fieldKey.getType();
		SetCellStyle style = fieldMap.get(fieldKey);
		if (String.class == filedClazz) {
			map.put(fieldKey.getName(), rowData[style.index()]);
		}
		else if (LocalDateTime.class == filedClazz) {
			map.put(fieldKey.getName(),
					LocalDateTime.parse(rowData[style.index()], PoiConstants.DATE_TIME_FORMAT));
		}
		else if (LocalDate.class == filedClazz) {
			map.put(fieldKey.getName(),
					LocalDate.parse(rowData[style.index()], PoiConstants.DATE_TIME_FORMAT));
		}
		else if (Integer.class == filedClazz) {
			map.put(fieldKey.getName(), Integer.parseInt(rowData[style.index()]));
		}
	}


	/**
	 * 读取Excel数据内容
	 * (默认第一行数据去除，标题头)
	 *
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public Map<Integer, List<String[]>> readExcelContent() {
		return readExcelContent(1);
	}

	/**
	 * 读取Excel数据内容
	 * (默认第一行数据去除，标题头)
	 *
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public Map<Integer, List<String[]>> readExcelContent(Integer lineNumber) {
		Map<Integer, List<String[]>> content = new HashMap<>(16);
		if (inputStream != null) {
			try {
				int sheetNum = workbook.getNumberOfSheets();
				for (int s = 0; s < sheetNum; s++) {
					List<String[]> sheetContent = new ArrayList<>();
					sheet = workbook.getSheetAt(s);
					// 得到总行数
					int rowNum = sheet.getLastRowNum();
					row = sheet.getRow(0);
					if (row != null) {
						int colNum = row.getPhysicalNumberOfCells();
						// 正文内容应该从第二行开始,第一行为表头的标题
						List<String> values;
						for (int i = lineNumber; i <= rowNum; i++) {
							row = sheet.getRow(i);
							if (row != null) {
								values = new ArrayList<>();
								int j = 0;
								while (j < colNum) {
									values.add(getStringCellValue(row.getCell(j)).trim());
									j++;
								}
								sheetContent.add(values.toArray(new String[0]));

							}
							content.put(s, sheetContent);
						}
					}
				}
			}
			catch (Exception e) {
				log.error("解析错误");
			}
		}
		return content;
	}

	/**
	 * 关闭文件流
	 *
	 * @return 关闭机构
	 */
	@SuppressWarnings("unused")
	public boolean close() {
		boolean flag = true;
		if (inputStream != null) {
			try {
				inputStream.close();
			}
			catch (IOException e) {
				flag = false;
				log.error("Excel文件解析，关闭文件流失败!", e);
			}
		}
		return flag;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 *
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getStringCellValue(Cell cell) {
//		String strCell = "";
//		if (cell != null) {
//			switch (cell.getCellType()) {
//				case STRING:
//					strCell = cell.getStringCellValue();
//					break;
//				case FORMULA:
//					String formulaValue = cell.getCellFormula();
//					if (formulaValue != null && !"".equals(formulaValue)) {
//						try {
//							strCell = String.valueOf(cell.getNumericCellValue());
//						}
//						catch (Exception e) {
//							strCell = cell.getStringCellValue();
//						}
//					}
//					break;
//				case NUMERIC:
//					if (DateUtil.isCellDateFormatted(cell)) {
//						strCell = getDateCellValue(cell);
//					}
//					else {
//						cell.setCellType(CellType.STRING);
//						String temp = cell.getStringCellValue();
//						if (temp.contains(PoiConstants.SPOT)) {
//							strCell = String.valueOf(new Double(temp)).trim();
//						}
//						else {
//							strCell = temp.trim();
//						}
//					}
//					break;
//				case BOOLEAN:
//					strCell = String.valueOf(cell.getBooleanCellValue());
//					break;
//				default:
//					strCell = "";
//					break;
//			}
//		}
//		return strCell;
		return null;
	}

	/**
	 * 获取单元格数据内容为日期类型的数据
	 *
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	private String getDateCellValue(Cell cell) {
		String result = "";
//		try {
//			CellType cellType = cell.getCellType();
//			if (cellType == NUMERIC) {
//				Date date = cell.getDateCellValue();
//				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//				result = sdf.format(date);
//			}
//			else if (cellType == STRING) {
//				String date = getStringCellValue(cell);
//				result = date.replaceAll("[年月]", "-").replace("日", "").trim();
//			}
//			else if (cellType == BLANK) {
//				result = "";
//			}
//		}
//		catch (Exception e) {
//			log.error("Excel文件解析失败，日期格式不正确!", e);
//		}
		return result;
	}
}